package com.shlh.saas.service;

import com.shlh.saas.entity.twitter.TwitterUser;
import com.shlh.saas.service.linkedin.LinkedInUserService;
import com.shlh.saas.service.twitter.TwitterService;
import jakarta.annotation.Resource;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;
import java.util.concurrent.CompletableFuture;
import java.util.concurrent.ExecutionException;

@Service
public class SocialMediaLookupService {

    private static final Logger log = LoggerFactory.getLogger(SocialMediaLookupService.class);

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Autowired
    private DataSource dataSource;

    @Resource
    private LinkedInUserService linkedInUserService;

    @Resource
    private TwitterService twitterService;

    // 创建直接连接到MySQL的方法，绕过ShardingSphere路由
    private Connection getDirectMySQLConnection(String dbName) {
        try {
            // 获取原始连接
            Connection conn = dataSource.getConnection();

            // 如果是包装的连接，可能需要获取原始连接
            // 使用USE语句切换到指定的数据库
            PreparedStatement useStmt = conn.prepareStatement("USE " + dbName);
            useStmt.execute();
            useStmt.close();

            return conn;
        } catch (SQLException e) {
            System.err.println("创建直接MySQL连接失败: " + e.getMessage());
            e.printStackTrace();
            return null;
        }
    }

    // 通用方法：结果集转换为Map
    private List<Map<String, Object>> resultSetToList(ResultSet rs) throws SQLException {
        List<Map<String, Object>> results = new ArrayList<>();

        // 获取结果集的元数据
        java.sql.ResultSetMetaData metaData = rs.getMetaData();
        int columnCount = metaData.getColumnCount();

        // 遍历结果集中的每一行
        while (rs.next()) {
            Map<String, Object> row = new HashMap<>();

            // 遍历当前行的每一列
            for (int i = 1; i <= columnCount; i++) {
                String columnName = metaData.getColumnName(i);
                Object value = rs.getObject(i);
                row.put(columnName, value);
            }

            results.add(row);
        }

        return results;
    }

    /**
     * 根据关键词查询社交媒体数据
     *
     * @param keyword 关键词
     * @param type    类型(linkedin/twitter)
     * @param page    页码
     * @param size    每页记录数
     * @return 查询结果和统计信息
     */
    public Map<String, Object> lookupSocialMedia(String keyword, String type, int page, int size) {
        // 计算分页参数
        int offset = (page - 1) * size;

        // 对关键词进行处理，支持模糊匹配
        String keywordPattern = "%" + keyword + "%";

        // 存储所有查询结果
        List<Map<String, Object>> allResults = new ArrayList<>();
        long totalCount = 0;
        List<String> errors = new ArrayList<>();

        try {
            System.out.println("开始社交媒体反查: type=" + type + ", keyword=" + keyword);

            if ("linkedin".equals(type)) {

                try {
                    List<Map<String, Object>> results = linkedInUserService.searchLinkedInUsersByKeyword(keyword, offset, size);

                    if (!results.isEmpty()) {
                        results.forEach(map -> map.put("source", "linkedin_data"));
                        allResults.addAll(results);

                        totalCount += linkedInUserService.countLinkedInUsersByKeyword(keyword);
                    }
                } catch (Exception e) {
                    System.err.println("LinkedIn数据查询失败: " + e.getMessage());
                    e.printStackTrace();
                    errors.add("LinkedIn数据查询失败: " + e.getMessage());
                }
            } else if ("twitter".equals(type)) {
                try {
                    List<Map<String, Object>> results = twitterService.searchByKeywordInAllFields(keyword, offset, size);
                    if (!results.isEmpty()) {
                        // 添加数据源标识
                        results.forEach(map -> map.put("source", "twitter_data"));

                        totalCount += twitterService.countTwitterUsersByKeyword(keyword);

                        allResults.addAll(results);
                    }
                } catch (Exception e) {
                    System.err.println("Twitter数据查询失败: " + e.getMessage());
                    e.printStackTrace();
                    errors.add("Twitter数据查询失败: " + e.getMessage());
                }
            }
        } catch (Exception e) {
            System.err.println("社交媒体查询过程中发生错误: " + e.getMessage());
            e.printStackTrace();
            errors.add("查询过程中发生错误: " + e.getMessage());
        }

        // 返回结果
        Map<String, Object> response = new HashMap<>();
        response.put("results", allResults);
        response.put("total", totalCount);
        response.put("page", page);
        response.put("size", size);

        // 如果有错误，添加到响应中
        if (!errors.isEmpty()) {
            response.put("errors", errors);
            // 只返回第一个错误作为主要错误信息
            response.put("error", errors.get(0));
        }

        return response;
    }

    /**
     * 批量社媒反查 - 根据领英链接和姓名查询LinkedIn数据
     */
    public List<Map<String, Object>> searchLinkedInByUrlAndName(String linkedinUrl, String name) {
        try {
            List<Map<String, Object>> results = linkedInUserService.searchByLinkedInUrlAndName(linkedinUrl, name, 0, 100);
            // 转换字段名以匹配Excel表头
            return results.stream().map(this::convertLinkedInMapForExcel).toList();
        } catch (Exception e) {
            log.error("查询LinkedIn数据失败: linkedinUrl={}, name={}", linkedinUrl, name, e);
            return new ArrayList<>();
        }
    }

    /**
     * 批量社媒反查 - 根据姓名和用户名查询Twitter数据（优化为批量ID查询）
     */
    public List<Map<String, Object>> searchTwitterByNameAndUsername(String name, String username) {
        try {
            List<Map<String, Object>> results = twitterService.searchByNameAndUsernameForIdentifiers(name, username, 100);
            
            if (results.isEmpty()) {
                return new ArrayList<>();
            }

            // 转换字段名以匹配Excel表头
            return results.stream().map(this::convertTwitterMapForExcel).toList();
        } catch (Exception e) {
            log.error("查询Twitter数据失败: name={}, username={}", name, username, e);
            return new ArrayList<>();
        }
    }


    // 数据转换方法
    private Map<String, Object> convertLinkedInMapForExcel(Map<String, Object> source) {
        Map<String, Object> result = new HashMap<>();
        result.put("公司名", source.get("company_name"));
        result.put("人名", source.get("full_name"));
        result.put("网址", source.get("company_website"));
        result.put("职位", source.get("job_title"));
        result.put("行业", source.get("industry"));
        result.put("邮箱", source.get("emails"));
        result.put("电话", source.get("phone_numbers"));
        result.put("手机号", source.get("mobile"));
        result.put("位置", source.get("location"));
        result.put("地点", source.get("street_address"));
        result.put("国家", source.get("countries"));
        result.put("公司规模", source.get("company_size"));
        result.put("twitter用户", source.get("twitter_user_name"));
        result.put("twitter链接", source.get("twitter_url"));
        result.put("LinkedIn链接", source.get("linkedin_url"));
        result.put("GitHub链接", source.get("github_url"));
        result.put("Facebook链接", source.get("facebook_url"));
        return result;
    }

    private Map<String, Object> convertTwitterMapForExcel(Map<String, Object> source) {
        Map<String, Object> result = new HashMap<>();
        result.put("姓名", source.get("name"));
        result.put("邮箱", source.get("email"));
        result.put("屏幕名", source.get("screen_name"));
        result.put("粉丝数", source.get("followers"));
        result.put("创建时间", source.get("created_at"));
        return result;
    }

//    public List<Map<String, Object>> searchLinkedInByNameOrUrl(String keyword) {
//        try {
//            List<Map<String, Object>> results = linkedInUserService.searchLinkedInByNameOrUrl(keyword, 0, 100);
//            // 转换字段名以匹配Excel表头
//            return results.stream().map(this::convertLinkedInMapForExcel).toList();
//        } catch (Exception e) {
//            e.printStackTrace();
//            return new ArrayList<>();
//        }
//    }
}